<?php


namespace commands;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class EmsImportFee extends BaseCommand
{

    protected static $defaultName = 'jkzdf';
    const BCJG = '国际业务分公司';
    const BCJG2 = '中国邮政集团公司四川省寄递事业部国际业务分公司单位领导部门';

    public function configure()
    {
        parent::configure();
        $this->setDescription('EMS进口终端费,压缩包文件：*_JKZDF.zip, *_JKYGF.zip, *_JKHHJCLF.zip');
    }

    public function execute(InputInterface $input, OutputInterface $output)
    {
        parent::execute($input, $output);
        $dir = $this->getDataFilePath($input->getArgument('dir'));
        $this->unzip(['*_JKZDF.zip', '*_JKYGF.zip', '*_JKHHJCLF.zip'], $dir, $output);
        //合并文件
        $output->writeln('开始计算');

        $this->execZdfEms($dir, $input, $output);
        $this->execZdfBg($dir, $input, $output);
        $this->execHhj($dir, $input, $output);
        $this->execYg($dir, $input, $output);
        $dest = $dir . '/jkzdf_qfb.xlsx';
        $this->export($dest, './tpl/emsjkzdf_qfb.xlsx', function (Spreadsheet $spreadsheet) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            $this->processExport($this->data, $first_row, $sheet);
        });
        $this->export($dir . '/jkzdf_qfb2.xlsx', './tpl/emsjkzdf_qfb.xlsx', function (Spreadsheet $spreadsheet) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            @$this->processExport($this->data2, $first_row, $sheet);
        });
        $this->rmTemp($dir, $output);
    }

    public function execZdfEms($dir, $input, OutputInterface $output)
    {
        $output->writeln('处理应收进口国际终端费EMS');
        $comboFile = $this->getDataFilePath($input->getArgument('dir') . '/temp/+.csv');
        $yzems = $this->findFile($dir . '/temp/', 'zdf_0-99999999.csv', '>2', '/yz/ems');
        $sdems = $this->findFile($dir . '/temp/', 'zdf_0-99999999.csv', '>2', '/sd/ems');
        comboCsv($yzems->getPathname(), $sdems->getPathname(), $comboFile);
        $this->doCsv($comboFile, $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $this->getCellFromRow($row, 'I');
            $this->processField('emshsje', $city, $row, $n);
            $this->processField('emsxxs', $city, $row, $n);
            $this->processField('emsbhsje', $city, $row, $n);
        });
    }

    public function execZdfBg($dir, $input, OutputInterface $output)
    {
        $output->writeln('处理应收进口国际终端费国际包裹');
        $comboFile2 = $this->getDataFilePath($input->getArgument('dir') . '/temp/+2.csv');
        $yzpac = $this->findFile($dir . '/temp/', 'zdf_0-99999999.csv', '>2', '/yz/pac');
        $sdpac = $this->findFile($dir . '/temp/', 'zdf_0-99999999.csv', '>2', '/sd/pac');

        comboCsv($yzpac->getPathname(), $sdpac->getPathname(), $comboFile2);
        $this->doCsv($comboFile2, $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $this->getCellFromRow($row, 'I');
            $this->processField('bkhsje', $city, $row, $n);
            $this->processField('bkxxs', $city, $row, $n);
            $this->processField('bkbhsje', $city, $row, $n);

        });
    }

    public function execHhj($dir, $input, OutputInterface $output)
    {
        $subtotal = [];
        $output->writeln('处理进口互换局');
        $file = $this->mustFindFile($dir . '/temp/', 'i_trans.sql0-99999999.csv', '>0', '_JKHHJCL');
        $this->doCsv($file, $output, function ($row, $n) use (&$subtotal) {
            if ($n == 0) return;
            //$city = $this->getDepartmentName($this->getCellFromRow($row, 'I'));
            $this->processRow('hhjhsje', $row, $n);
            $this->processRow('hhjxxs', $row, $n);
            $this->processRow('hhjbhsje', $row, $n);
            $this->processRow('hhjbghsje', $row, $n);
            $this->processRow('hhjbgxxs', $row, $n);
            $this->processRow('hhjbgbhsje', $row, $n);

        });
    }

    public function execYg($dir, $input, OutputInterface $output)
    {
        $output->writeln('处理进口验关数据');
        $file = $this->mustFindFile($dir . '/temp/', 'i_customs.sql0-99999999.csv', '>0', '_JKYGF');
        $this->doCsv($file->getPathname(), $output, function ($row, $n) {
            if ($n == 0) return;
            //$city = $this->getDepartmentName($this->getCellFromRow($row, 'I'));
            $this->processRow('yghsje', $row, $n);
            $this->processRow('ygxxs', $row, $n);
            $this->processRow('ygbhsje', $row, $n);
            $this->processRow('ygbghsje', $row, $n);
            $this->processRow('ygbgxxs', $row, $n);
            $this->processRow('ygbgbhsje', $row, $n);
        });
    }

    public function emshsje($row)
    {
        return floatval($this->getCellFromRow($row, 'O'));
    }

    public function emsxxs($row)
    {
        return round(floatval($this->getCellFromRow($row, 'O')) / 1.06, 2);
    }

    public function emsbhsje($row)
    {
        return floatval($this->getCellFromRow($row, 'O')) - $this->emsxxs($row);
    }

    public function bkhsje($row)
    {
        return floatval($this->getCellFromRow($row, 'S'));
    }

    public function bkxxs($row)
    {
        return round(floatval($this->getCellFromRow($row, 'S')) / 1.06, 2);
    }

    public function bkbhsje($row)
    {
        return floatval($this->getCellFromRow($row, 's')) - $this->bkxxs($row);
    }

    /**
     * P
     * @param $row
     * @return float|int
     * @throws \Exception
     */
    public function hhjhsje($row)
    {
        if (iconvStr(trim($this->getCellFromRow($row, 'a'))) == '特快专递') {
            return floatval($this->getCellFromRow($row, 'N'));
        }
        return 0;
    }

    /**
     * Q
     * @param $row
     * @return float
     * @throws \Exception
     */
    public function hhjxxs($row)
    {
        return round($this->hhjhsje($row) / 1.06, 2);
    }

    public function hhjbhsje($row)
    {
        return $this->hhjhsje($row) - $this->hhjxxs($row);

    }

    /**
     * S
     * @param $row
     * @return float|int
     * @throws \Exception
     */
    public function hhjbghsje($row)
    {
        if (iconvStr(trim($this->getCellFromRow($row, 'a'))) == '包裹') {
            return floatval($this->getCellFromRow($row, 'N'));
        }
        return 0;
    }

    /**
     * T
     * @param $row
     * @return float
     * @throws \Exception
     */
    public function hhjbgxxs($row)
    {
        return round($this->hhjbghsje($row) / 1.06, 2);
    }

    public function hhjbgbhsje($row)
    {
        return $this->hhjbghsje($row) - $this->hhjbgxxs($row);
    }

    /**
     * 验关Q
     * @param $row
     * @return float|int
     * @throws \Exception
     */
    public function yghsje($row)
    {
        if (iconvStr($this->getCellFromRow($row, 'A')) == '特快专递') {
            return floatval($this->getCellFromRow($row, 'n'));
        }
        return 0;
    }

    /**
     * R 验关
     * @param $row
     * @return float
     * @throws \Exception
     */
    public function ygxxs($row)
    {
        return round($this->yghsje($row) / 1.06, 2);
    }

    /**
     * S 验关
     * @param $row
     * @return float|int
     * @throws \Exception
     */
    public function ygbhsje($row)
    {
        return $this->yghsje($row) - $this->ygxxs($row);
    }

    /**
     * T 验关
     * @param $row
     * @return float|int
     * @throws \Exception
     */
    public function ygbghsje($row)
    {
        if (iconvStr($this->getCellFromRow($row, 'A')) == '包裹') {
            return floatval($this->getCellFromRow($row, 'N'));
        }
        return 0;
    }

    /**
     * U 验关
     * @param $row
     * @return float
     * @throws \Exception
     */
    public function ygbgxxs($row)
    {
        return round($this->ygbghsje($row) / 1.06, 2);
    }

    /**
     * V
     * @param $row
     * @return float|int
     * @throws \Exception
     */
    public function ygbgbhsje($row)
    {
        return $this->ygbghsje($row) - $this->ygbgxxs($row);
    }

    public function processExport($array, $first_row, Worksheet $sheet)
    {
        $row = $first_row;
        foreach ($array as $city => $data) {
            if (!is_array($data)) continue;
            $sheet->setCellValue('A' . $row, $city);
            $sheet->setCellValueExplicit('B' . $row, round($data['emshsje'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('C' . $row, round($data['emsxxs'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('D' . $row, round($data['emsbhsje'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('E' . $row, round($data['bkhsje'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('F' . $row, round($data['bkxxs'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('G' . $row, round($data['bkbhsje'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('H' . $row, sprintf('=SUM(B%d+E%d)', $row, $row));
            if ($city == self::BCJG) {
                $sheet->setCellValueExplicit('I' . $row, round($array['hhjhsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('J' . $row, round($array['hhjxxs'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('K' . $row, round($array['hhjbhsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('L' . $row, round($array['hhjbghsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('M' . $row, round($array['hhjbgxxs'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('N' . $row, round($array['hhjbgbhsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValue('O' . $row, sprintf('=SUM(I%d+L%d)', $row, $row));
                $sheet->setCellValueExplicit('P' . $row, round($array['yghsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('Q' . $row, round($array['ygxxs'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('R' . $row, round($array['ygbhsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('S' . $row, round($array['ygbghsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('T' . $row, round($array['ygbgxxs'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValueExplicit('U' . $row, round($array['ygbgbhsje'], 2), DataType::TYPE_NUMERIC);
                $sheet->setCellValue('V' . $row, sprintf('=SUM(P%d+S%d)', $row, $row));

            }
            $sheet->setCellValue('W' . $row, sprintf('=SUM(V%d+O%d+H%d)', $row, $row, $row));
            $row++;
        }
        $this->setSheetSubtotal($sheet, $first_row, $row, 'W');
    }
}
